package cn.wjzhang.gen;

import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.Date;
import java.util.*;

@Slf4j
public class DbUtils {
    private static final HashMap<String, String> JAVA_TYPE_MAP = new HashMap<>();

    static {
        // String
        JAVA_TYPE_MAP.put("CHAR", "String");
        JAVA_TYPE_MAP.put("VARCHAR", "String");
        JAVA_TYPE_MAP.put("BLOB", "String");
        JAVA_TYPE_MAP.put("TEXT", "String");
        JAVA_TYPE_MAP.put("LONGTEXT", "String");
        // Integer
        JAVA_TYPE_MAP.put("TINYINT", "Integer");
        JAVA_TYPE_MAP.put("INT", "Integer");
        JAVA_TYPE_MAP.put("BIT", "Integer");
        // Long
        JAVA_TYPE_MAP.put("BIGINT", "Long");
        JAVA_TYPE_MAP.put("BIGINT UNSIGNED", "Long");
        // BigDecimal
        JAVA_TYPE_MAP.put("DECIMAL", "BigDecimal");
        // Date
        JAVA_TYPE_MAP.put("DATETIME", "Date");
        JAVA_TYPE_MAP.put("DATE", "Date");
        JAVA_TYPE_MAP.put("TIME", "Date");
    }

    private static final List<String> NO_COLUMNS_ADD = Arrays.asList( //实体类中不生成的字段
            new String[]{"del_flag", "create_by", "create_time", "update_by", "update_time"});

    private static final String SQL = "SELECT * FROM ";// 数据库操作
    // 字段SQL
    private static final String COLUMNS_SQL = "SELECT column_name COLUMN_NAME, data_type DATA_TYPE, is_nullable IS_NULLABLE, column_comment DESCRIPTION FROM information_schema.columns ";

    private String host;
    private String port;
    private String username;
    private String password;
    private String dbName;


    public DbUtils(String host, String port, String username, String password, String dbName, String driveName) {
        this.host = host;
        this.port = port;
        this.username = username;
        this.password = password;
        this.dbName = dbName;

        try {
            Class.forName(driveName);
        } catch (ClassNotFoundException e) {
            log.error("can not load jdbc driver", e);
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection() {
        Connection conn = null;
        try {
            log.info("JDBC连接地址为------> jdbc:mysql://" + this.host + ":" + this.port + (StringUtils.isNotBlank(this.dbName) ? "/" + this.dbName : "") + "?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", this.username, this.password);
            conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + (StringUtils.isNotBlank(dbName) ? "/" + dbName : "") + "?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", username, password);
        } catch (SQLException e) {
            log.error("get connection failure", e);
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure", e);
            }
        }
    }

    /**
     * 获取所有数据库名
     */
    public List<String> getDatabases() {
        List<String> databases = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            // 获取所有数据库名
            rs = stmt.executeQuery("show databases;");
            ResultSetMetaData rs_metaData = rs.getMetaData();

            // 将数据库名放入集合
            while (rs.next()) {
                int count = rs_metaData.getColumnCount();
                for (int i=0;i<count;i++)
                {
                    databases.add(rs.getString(i+1));
                }
            }
        } catch (SQLException e) {
            log.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return databases;
    }

    /**
     * 获取数据库下的所有表名
     */
    public List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(conn.getCatalog(), null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            log.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

    /**
     * 获取数据库下的所有表名
     */
    public List<ColumnInfo> getColumns(String tableName) throws Exception{
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = COLUMNS_SQL + " WHERE table_schema = '"+ dbName + "' AND table_name = '" + tableName + "' ORDER BY ordinal_position";
        List<ColumnInfo> columnInfoList = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery();

            while (rs.next()) {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setName(rs.getString("COLUMN_NAME"));
                columnInfo.setDesc(rs.getString("DESCRIPTION"));
                columnInfo.setJdbcType(rs.getString("DATA_TYPE"));
                columnInfo.setJavaType(JAVA_TYPE_MAP.get(rs.getString("DATA_TYPE").toUpperCase(Locale.ROOT)));
                if (columnInfo.getJavaType() == null) {
                    log.error("{}, 没有配置对应javaType", columnInfo.getJdbcType());
                    throw new Exception(columnInfo.getJdbcType() + "没有配置对应javaType");
                }
                columnInfo.setJavaName(underlineToCamel(columnInfo.getJavaType()));
                String isNullable = rs.getString("IS_NULLABLE");
                // Column类的isNullable为true表示不允许为空
                if("N".equalsIgnoreCase(isNullable) || "NO".equalsIgnoreCase(isNullable)
                        || "NOT".equalsIgnoreCase(isNullable)) {
                    columnInfo.setIsNull(0);
                } else {
                    columnInfo.setIsNull(1);
                }
                columnInfoList.add(columnInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumns close ResultSet and connection failure", e);
                }
            }
        }
        return columnInfoList;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return
     */
    private List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段是否必填
     *
     * @param tableName 表名
     * @return
     */
    private List<Integer> getColumnIsNull(String tableName) {
        List<Integer> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.isNullable(i+1));
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    private List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    private List<String> getColumnComments(String tableName) {
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }

    private Boolean isExistTb(String tableName) {
        String sql = "Select count(1) from INFORMATION_SCHEMA.TABLES Where table_schema = '" + dbName + "'##数据库名\n" +
                "AND table_name ='" + tableName + "'##表名";
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        ResultSet resultSet = null;

        Boolean isExist = false;
        try {
            pStemt = conn.prepareStatement(sql);
            resultSet = pStemt.executeQuery();
            while (resultSet.next()) {
                isExist = resultSet.getInt(1) == 1;
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getTableDesc close pstem and connection failure", e);
                }
            }
        }
        return isExist;
    }

    private String getTableDesc(String tableName) {
        String sql = "Select table_name 表名,TABLE_COMMENT 表注释 from INFORMATION_SCHEMA.TABLES Where table_schema = '" + dbName + "'##数据库名\n" +
                "AND table_name ='" + tableName + "'##表名";
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        ResultSet resultSet = null;

        String tbDesc = "";
        try {
            pStemt = conn.prepareStatement(sql);
            resultSet = pStemt.executeQuery();
            while (resultSet.next()) {
                tbDesc = resultSet.getString(2);
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getTableDesc close pstem and connection failure", e);
                }
            }
        }
        return tbDesc;
    }

    private static final char UNDERLINE = '_';

    /**
     * 下划线 转 驼峰
     *
     * @param param
     * @return
     */
    private String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = Character.toLowerCase(param.charAt(i));
            if (c == UNDERLINE) {
                if (++i < len) {
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    public TbInfo getTbInfo(String tableName) throws Exception {
        if (!isExistTb(tableName)) {
            log.error("{} 不存在\n", tableName);
            return null;
        }

        TbInfo tbInfo = new TbInfo();

        tbInfo.setTbName(tableName).setTbDesc(getTableDesc(tableName)).setDbName(this.dbName);

        List<ColumnInfo> columns = new ArrayList<>();
        List<String> columnNameList = getColumnNames(tableName);
        List<String> columnTypeList = getColumnTypes(tableName);
        List<String> columnCommentList = getColumnComments(tableName);
        List<Integer> columnIsNullList = getColumnIsNull(tableName);
        for (int i = 0; i < columnNameList.size(); i++) {
            ColumnInfo column = new ColumnInfo();
            column.setName(columnNameList.get(i))
                    .setDesc(columnCommentList.get(i))
                    .setJdbcType(columnTypeList.get(i))
                    .setJavaType(JAVA_TYPE_MAP.get(columnTypeList.get(i)))
                    .setJavaName(underlineToCamel(columnNameList.get(i)))
                    .setGenFlag(1);
            if (column.getJavaType() == null) {
                log.error("{}, 没有配置对应javaType", column.getJdbcType());
                throw new Exception(column.getJdbcType() + "没有配置对应javaType");
            }
            column.setIsNull(columnIsNullList.get(i));
            columns.add(column);
        }
        tbInfo.setColumnList(columns);

        tbInfo.setJavaName(underlineToCamel(tableName));
        tbInfo.setJavaNameBig(tbInfo.getJavaName().substring(0, 1).toUpperCase() + tbInfo.getJavaName().substring(1));

        tbInfo.setAuthor("zft");
        tbInfo.setEmail("370097879@qq.com");
        tbInfo.setDate(DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
        return tbInfo;
    }
}
